/**
 *
 */
package user_client;

import database_objects.Author;
import database_objects.Book;
import database_objects.Department;
import database_objects.EditionBook;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import oracle.jdbc.internal.ClientDataSupport;

//import database_objects.Department;
//import library.DepartmentManagerPane;
/**
 *
 * @author Pawel Rychly
 *
 */
public abstract class DataBaseClient {

    /**
     * Represents connection to database
     */
    private Connection connection;
    private String password = null;
    private String userName = null;
    
    public DataBaseClient(Connection connection, String userName, String password) {
        this.connection = connection;
        this.userName = userName;
        this.password = password;
    }

    //returns informations about user credibility
    private boolean checkUserCredibility() {
        //TODO: definition of this function 
        return true;
    }

    /**
     * Function finds a book in database;
     */
    public void searchBook() {
        //TODO: definition of this function
    }

    /**
     * Function returns a Connection object.
     *
     * @return
     */
    public Connection getConnection() {
        return connection;
    }

    /**
     * Returns a password of this client.
     *
     * @return password.
     */
    public String getPassword() {
        return password;
    }

    /**
     * Returns name of this client.
     *
     * @return name.
     */
    public String getUserName() {
        return userName;
    }
    
    private ResultSet simpleSearchValues(ArrayList<String> cols,
            ArrayList<String> values,
            String nameOfTable) throws SQLException {
        
        String whereCondition = "";        
        for (Integer i = 0; i < cols.size(); i++) {
            if (i < values.size() && (values.get(i) != null)) {
                if (i > 0) {
                    whereCondition += " and ";
                }
                String value = values.get(i);
                whereCondition += ("( lower(substr(" + cols.get(i)
                        + ", 0, " + value.length() + ")) = lower('" + value + "' ))");
                
            }
        }
        if (whereCondition.length() > 0) {
            whereCondition = " where " + whereCondition;
        }
        System.out.println("where condition:");
        System.out.println(whereCondition);
        
        
        Statement stmt = this.connection.createStatement();
        ResultSet res = stmt.executeQuery("select * from " + nameOfTable + whereCondition);
        
        
        return res;
    }
    
    public Department getDepartment(Integer id) throws SQLException {
        
        ArrayList<String> cols = new ArrayList<String>();
        ArrayList<String> values = new ArrayList<String>();
        
        cols.add("id_dzialu");
        values.add(id.toString());
        
        ResultSet result = simpleSearchValues(cols, values, "dzialy");
        Department department = null;
        while (result.next()) {
            department = new Department(
                    result.getString("nazwa"),
                    result.getString("id_dzialu"),
                    result.getString("nr_regalu"));
        }
        
        result.close();
        

        return department;
    }
    
    public ArrayList<Department> searchDepartments(
            String nameValue, String numValue) throws SQLException {
        ArrayList<String> rows = new ArrayList<String>();
        
        ArrayList<String> values = new ArrayList<String>();
        if (nameValue.length() > 0) {
            rows.add("nazwa");
            values.add(nameValue);
        }
        
        if (numValue.length() > 0) {
            rows.add("nr_regalu");
            values.add(numValue);
        }
        
        values.add(numValue);
        
        ResultSet result = simpleSearchValues(rows, values, "dzialy");
        ArrayList<Department> res = new ArrayList<Department>();
        while (result.next()) {
            res.add(new Department(
                    result.getString("nazwa"),
                    result.getString("id_dzialu"),
                    result.getString("nr_regalu")));
        }
        
        result.close();
        
        return res;
        
    }
    
    
    
    public ArrayList<Author> searchAuthors(
            String nameValue, String numValue) throws SQLException {
        ArrayList<String> rows = new ArrayList<String>();
        
        ArrayList<String> values = new ArrayList<String>();
        if (nameValue.length() > 0) {
            rows.add("imie");
            values.add(nameValue);
        }
        
        if (numValue.length() > 0) {
            rows.add("nazwisko");
            values.add(numValue);
        }
        
        values.add(numValue);
        
        ResultSet result = simpleSearchValues(rows, values, "autorzy");
        ArrayList<Author> res = new ArrayList<Author>();
        while (result.next()) {
            res.add(new Author(
                    result.getString("id_autora"),
                    result.getString("imie"),
                    result.getString("nazwisko")));
        }
        
        result.close();
        
        return res;
        
    }
    
    public ArrayList<Book> searchBooks(
            String isbn, String nameOfEdition, String dateOfEdition,
            String title, String idOfDepartment, String nameValue,
            String numValue, String authorName, String authorSurname,
            String positionOnboard
            ){
        
        ArrayList<String> cols = new ArrayList<String>();
        ArrayList<String> values = new ArrayList<String>();
        
        if (isbn.length() > 0) {
            cols.add("isbn");
            values.add(isbn);
        }
        
        if (nameOfEdition.length() > 0) {
            cols.add("wydawnictwo");
            values.add(nameOfEdition);
        }
        
        if (dateOfEdition.length() > 0) {
            cols.add("data_wydania");
            values.add(dateOfEdition);
        }
        
        if (title.length() > 0) {
            cols.add("tytul");
            values.add(title);
        }
        
        if (idOfDepartment.length() > 0) {
            cols.add("id_dzialu");
            values.add(idOfDepartment);
        }
        
        if (nameValue.length() > 0) {
            cols.add("tytul");
            values.add(title);
        }
        
        if (idOfDepartment.length() > 0) {
            cols.add("id_dzialu");
            values.add(idOfDepartment);
        }
    }
    
    public ArrayList<EditionBook> searchEditions(
            String isbn,
            String nameOfEdition,
            String dateOfEdition,
            String title,
            String idOfDepartment) throws SQLException {
        ArrayList<String> rows = new ArrayList<String>();
        
        ArrayList<String> values = new ArrayList<String>();
        if (isbn.length() > 0) {
            rows.add("isbn");
            values.add(isbn);
        }
        
        if (nameOfEdition.length() > 0) {
            rows.add("wydawnictwo");
            values.add(nameOfEdition);
        }
        
        if (dateOfEdition.length() > 0) {
            rows.add("data_wydania");
            values.add(dateOfEdition);
        }
        
        if (title.length() > 0) {
            rows.add("tytul");
            values.add(title);
        }
        
        if (idOfDepartment.length() > 0) {
            rows.add("id_dzialu");
            values.add(idOfDepartment);
        }

        //values.add(numValue);
        
        ResultSet result = simpleSearchValues(rows, values, "wydania");
        ArrayList<EditionBook> res = new ArrayList<EditionBook>();
        while (result.next()) {
            res.add(new EditionBook(result.getString("isbn"),
                    result.getString("wydawnictwo"),
                    result.getString("data_wydania"),
                    result.getString("tytul"),
                    result.getString("id_dzialu")));
        }
        
        result.close();
        
        return res;
        
    }
    
    public void removeElement(
            String id, String nameOfTable, String col) throws SQLException {
        Statement stmt = this.connection.createStatement();
        int val = stmt.executeUpdate("DELETE from "
                + nameOfTable + " where (" + col + " = " + id + ")");
        connection.commit();
        stmt.close();
        
    }
    
    protected boolean isExists(ArrayList<String> cols,
            ArrayList<String> values,
            String nameOfTable) throws SQLException {
        
        String whereCondition = "";        
        for (Integer i = 0; i < cols.size(); i++) {
            if (i < values.size() && (values.get(i) != null)) {
                if (i > 0) {
                    whereCondition += " and ";
                }
                String value = values.get(i);
                whereCondition += ("( lower(substr(" + cols.get(i)
                        + ", 0, " + value.length() + ")) = lower('" + value + "' ))");
                
            }
        }
        if (whereCondition.length() > 0) {
            whereCondition = " where " + whereCondition;
        }
        System.out.println("where condition:");
        System.out.println(whereCondition);
        
        
        Statement stmt = this.connection.createStatement();
        ResultSet res = stmt.executeQuery("select count(*) as num from " + nameOfTable + whereCondition);
        res.next();
        Integer size = res.getInt("num");
        res.close();
        stmt.close();
        if (size > 0) {
            return true;
        } else {
            return false;
        }
    }
}
